namespace SYMetro.Repository.EntityFramework.Migrations
{
    using System;
    using System.Data.Entity.Migrations;
    
    public partial class Initial : DbMigration
    {
        public override void Up()
        {
            #region custom sql


            Sql(@"
CREATE FUNCTION [dbo].[Roles_Ancestor_TVF]
(
	@RoleId uniqueidentifier
)
RETURNS 
	@Roles TABLE 
	(
		 RoleId uniqueidentifier
		 , RoleName nvarchar(256)
		 , LoweredRoleName nvarchar(256)
		, Description nvarchar(256)
		, ParentRoleId uniqueidentifier
	)
	AS
	BEGIN
	  ; WITH Roles_CTE 
	  (
		 RoleId
		   , RoleName
		   , LoweredRoleName
		   , Description
		   , ParentRoleId
		   , HierarchyLevel
		) AS
		 (
		   SELECT
			 Id
			   , RoleName
			   , LoweredRoleName
			   , Description
			  , Parent_Id
			  , 1 AS HierarchyLevel
			FROM Roles
		   WHERE Id = @RoleId	 

			UNION ALL
	 
			SELECT
				Roles.Id
				, Roles.RoleName
				, Roles.LoweredRoleName
				, Roles.Description
			   , Roles.Parent_Id
			   , Roles_CTE.HierarchyLevel + 1 AS HierarchyLevel
		   FROM Roles INNER JOIN Roles_CTE
				ON Roles.Id = Roles_CTE.ParentRoleId
		)
			 
		INSERT INTO @Roles (
			 RoleId
			, RoleName
			, LoweredRoleName
			, Description
		   , ParentRoleId
		)
		SELECT
			RoleId
			, RoleName
			, LoweredRoleName
			, Description
			, ParentRoleId
	   FROM Roles_CTE
			ORDER BY HierarchyLevel 		
		RETURN 
	END
");

            Sql(@"
CREATE FUNCTION [dbo].[Roles_Descendant_TVF]
(
	@RoleId uniqueidentifier
)
RETURNS 
	@Roles TABLE (
	 RoleId uniqueidentifier
	, RoleName nvarchar(256)
	, LoweredRoleName nvarchar(256)
	, Description nvarchar(256)
	, ParentRoleId uniqueidentifier
)
AS
BEGIN
	; WITH Roles_CTE 
	(
		 RoleId
		, RoleName
		, LoweredRoleName
		, Description
		, ParentRoleId
		, HierarchyLevel
	) 
	AS 
	(
		SELECT
			Id
			, RoleName
			, LoweredRoleName
			, Description
			, Parent_Id
			, 1 AS HierarchyLevel
		FROM Roles
		WHERE Id = @RoleId	 

		UNION ALL	 

		SELECT
			 Roles.Id
			, Roles.RoleName
			, Roles.LoweredRoleName
			, Roles.Description
			, Roles.Parent_Id
			, Roles_CTE.HierarchyLevel + 1 AS HierarchyLevel
		FROM Roles INNER JOIN Roles_CTE
			ON Roles.Parent_Id = Roles_CTE.RoleId
	)	 

	INSERT INTO @Roles (
		 RoleId
		, RoleName
		, LoweredRoleName
		, Description
		, ParentRoleId
	)
	SELECT
		RoleId
		, RoleName
		, LoweredRoleName
		, Description
		, ParentRoleId
	FROM Roles_CTE
		ORDER BY HierarchyLevel 		
	RETURN 
END
");

            #endregion

            CreateTable(
                "dbo.Roles",
                c => new
                    {
                        ID = c.Guid(nullable: false, identity: true),
                        RoleName = c.String(),
                        LoweredRoleName = c.String(),
                        Description = c.String(),
                        Flag = c.Int(nullable: false),
                        Sequence = c.String(),
                        Parent_ID = c.Guid(),
                    })
                .PrimaryKey(t => t.ID)
                .ForeignKey("dbo.Roles", t => t.Parent_ID)
                .Index(t => t.Parent_ID);
            
            CreateTable(
                "dbo.Users",
                c => new
                    {
                        ID = c.Guid(nullable: false, identity: true),
                        PasswordFormat = c.Int(nullable: false),
                        MobilePIN = c.String(),
                        Email = c.String(),
                        LoweredEmail = c.String(),
                        PasswordQuestion = c.String(),
                        PasswordAnswer = c.String(),
                        IsApproved = c.Boolean(nullable: false),
                        IsLockedOut = c.Boolean(nullable: false),
                        CreateDate = c.DateTime(nullable: false),
                        LastLoginDate = c.DateTime(),
                        LastPasswordChangedDate = c.DateTime(),
                        LastLockoutDate = c.DateTime(),
                        FailedPasswordAttemptCount = c.Int(nullable: false),
                        FailedPasswordAttemptWindowStart = c.DateTime(),
                        FailedPasswordAnswerAttemptCount = c.Int(nullable: false),
                        FailedPasswordAnswerAttemptWindowStart = c.DateTime(),
                        Comment = c.String(),
                        UserName = c.String(),
                        DisplayName = c.String(),
                        MobileAlias = c.String(),
                        IsAnonymous = c.Boolean(nullable: false),
                        LastActivityDate = c.DateTime(),
                        LoweredUserName = c.String(),
                        Password = c.String(),
                    })
                .PrimaryKey(t => t.ID);
            
            CreateTable(
                "dbo.UserInRoles",
                c => new
                    {
                        Role_ID = c.Guid(nullable: false),
                        User_ID = c.Guid(nullable: false),
                    })
                .PrimaryKey(t => new { t.Role_ID, t.User_ID })
                .ForeignKey("dbo.Roles", t => t.Role_ID, cascadeDelete: true)
                .ForeignKey("dbo.Users", t => t.User_ID, cascadeDelete: true)
                .Index(t => t.Role_ID)
                .Index(t => t.User_ID);
            
        }
        
        public override void Down()
        {
            DropIndex("dbo.UserInRoles", new[] { "User_ID" });
            DropIndex("dbo.UserInRoles", new[] { "Role_ID" });
            DropIndex("dbo.Roles", new[] { "Parent_ID" });
            DropForeignKey("dbo.UserInRoles", "User_ID", "dbo.Users");
            DropForeignKey("dbo.UserInRoles", "Role_ID", "dbo.Roles");
            DropForeignKey("dbo.Roles", "Parent_ID", "dbo.Roles");
            DropTable("dbo.UserInRoles");
            DropTable("dbo.Users");
            DropTable("dbo.Roles");
        }
    }
}
